package com.edu.dao.impl;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import com.edu.dao.BaseDao;
import com.edu.dao.ReasonDao;
import com.edu.entity.GreenUser;
import com.edu.entity.Reason;
import com.edu.entity.ReasonTa;
import com.edu.entity.YuanC;


public class ReasonDaoImpl implements ReasonDao {

	@Override
	public List<Reason> allInformation(int id) {
		// TODO Auto-generated method stub
		List<Reason> userList = new ArrayList<Reason>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations where id="+id;
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Reason user = new Reason();
				user.setId(rs.getInt("id"));
				user.setAuthor(rs.getString("author"));
				user.setCollect(rs.getInt("collect"));
				user.setImg(rs.getString("img"));
				user.setTargets(rs.getInt("targets"));
				user.setTheme(rs.getString("theme"));
				user.setTitle(rs.getString("title"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<ReasonTa> allInTa(int id) {
		// TODO Auto-generated method stub
		List<ReasonTa> userList = new ArrayList<ReasonTa>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donationsContent where id="+id;
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				ReasonTa user = new ReasonTa();
				user.setId(rs.getInt("id"));
				user.setDonationsContent(rs.getString("donationsContent"));
				user.setDonationsContents(rs.getString("donationsContents"));
				user.setImg(rs.getString("img"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<Reason> allFor() {
		// TODO Auto-generated method stub
		List<Reason> userList = new ArrayList<Reason>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations ORDER BY id DESC LIMIT 0, 3";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Reason user = new Reason();
				user.setId(rs.getInt("id"));
				user.setAuthor(rs.getString("author"));
				user.setCollect(rs.getInt("collect"));
				user.setImg(rs.getString("img"));
				user.setTargets(rs.getInt("targets"));
				user.setTheme(rs.getString("theme"));
				user.setTitle(rs.getString("title"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<Reason> allFors() {
		// TODO Auto-generated method stub
		List<Reason> userList = new ArrayList<Reason>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations ORDER BY id DESC";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Reason user = new Reason();
				user.setId(rs.getInt("id"));
				user.setAuthor(rs.getString("author"));
				user.setCollect(rs.getInt("collect"));
				user.setImg(rs.getString("img"));
				user.setTargets(rs.getInt("targets"));
				user.setTheme(rs.getString("theme"));
				user.setTitle(rs.getString("title"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<GreenUser> forMoney(String name) {
		// TODO Auto-generated method stub
		List<GreenUser> userList = new ArrayList<GreenUser>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT SUM(donationMoney) AS sum FROM contribution WHERE donationAmount ='"+name+"'";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				GreenUser user = new GreenUser();
				user.setDonationMoney(rs.getInt("sum"));
				userList.add(user);
				break;
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public int allInformation(String name,int num) {
		// TODO Auto-generated method stub
			String sql = "UPDATE donations SET collect = collect + '"+num+"' WHERE theme = '"+name+"'";
			System.out.println(sql);
			int nums=BaseDao.executeUpdate(sql);
			return nums;
	}

	@Override
	public List<Reason> all() {
		// TODO Auto-generated method stub
		List<Reason> userList = new ArrayList<Reason>();
		try {
			Connection conn = BaseDao.getConn();
			Statement ps = conn.createStatement();
			String sql = "SELECT * FROM donations";
			System.out.println(sql);
			ResultSet rs = ps.executeQuery(sql);
			while (rs.next()) {
				Reason user = new Reason();
				user.setTheme(rs.getString("theme"));
				userList.add(user);
			}
			BaseDao.closeAll(conn, ps, rs);
		} catch (Exception e) {
			// TODO: handle exception
			e.printStackTrace();
		}
		return userList;
	}

	@Override
	public List<YuanC> allC() {
		// TODO Auto-generated method stub
		 List<YuanC> userList = new ArrayList<>();
		    Map<Integer, YuanC> userMap = new HashMap<>(); // 使用 map 来合并相同 ID 的记录

		    try {
		        Connection conn = BaseDao.getConn();
		        Statement ps = conn.createStatement();

		        // 第一个查询
		        String sql1 = "SELECT * FROM donations";
		        System.out.println(sql1);
		        ResultSet rs1 = ps.executeQuery(sql1);
		        while (rs1.next()) {
		            int id = rs1.getInt("id");
		            if (!userMap.containsKey(id)) {
		                YuanC user = new YuanC();
		                user.setId(id);
		                user.setAuthor(rs1.getString("author"));
		                user.setCollect(rs1.getInt("collect"));
		                user.setTargets(rs1.getInt("targets"));
		                user.setTheme(rs1.getString("theme"));
		                user.setTitle(rs1.getString("title"));
		                userMap.put(id, user);
		            }
		        }
		        rs1.close();  // 关闭第一个结果集

		        // 第二个查询
		        String sql2 = "SELECT * FROM donationsContent";
		        System.out.println(sql2);
		        ResultSet rs2 = ps.executeQuery(sql2);
		        while (rs2.next()) {
		            int id = rs2.getInt("id");
		            if (userMap.containsKey(id)) {
		                YuanC user = userMap.get(id);
		                user.setNei(rs2.getString("donationsContent"));  // 假设 donationsContent 表中有 nei 字段
		                user.setYuanNei(rs2.getString("donationsContents"));  // 假设 donationsContent 表中有 yuanNei 字段
		            } else {
		                // 如果没有对应的 donations 记录，则创建新的 YuanC 对象
		                YuanC user = new YuanC();
		                user.setId(id);
		                user.setNei(rs2.getString("nei"));  // 假设 donationsContent 表中有 nei 字段
		                user.setYuanNei(rs2.getString("yuanNei"));  // 假设 donationsContent 表中有 yuanNei 字段
		                userList.add(user);
		                userMap.put(id, user);
		            }
		        }
		        rs2.close();  // 关闭第二个结果集

		        // 关闭资源
		        BaseDao.closeAll(conn, ps, null);
		    } catch (SQLException e) {
		        e.printStackTrace();
		    }

		    // 将 map 中的值添加到列表中
		    userList.addAll(userMap.values());

		    return userList;

	}

	



}
